﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using CallCenter.Data.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Hosting;

namespace CallCenter.Data.BizUtilities
{
    public class CommunicationUtility : BaseUtility
    {
        public CommunicationUtility(DataManager manager)
            : base(manager)
        { }

        public ReturnMessage<CompanyCommunicationViewModel> InsertToCompanyComm(CompanyCommunicationViewModel model)
        {
            var returnMessage = new ReturnMessage<CompanyCommunicationViewModel>();

            if (model.emailTo == "")
                model.emailTo = "resnav@resnav.com";

            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";
            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";
            if (String.IsNullOrEmpty(model.subject))
                model.subject = "";
            if (String.IsNullOrEmpty(model.logInOwner))
                model.logInOwner = "";
            if (String.IsNullOrEmpty(model.logInOwner))
                model.logInOwner = "";
            if (String.IsNullOrEmpty(model.note))
                model.note = "";

            if (String.IsNullOrEmpty(model.emailTo))
                model.emailTo = "";
            if (model.emailTo == "")
                model.emailTo = "resnav@resnav.com";

            model.createdDate = DateTime.Now;


            var id = this.Insert(model.userID, model.logInOwner,model.createdDate,model.inOrOut, model.note, model.companyId,
                           model.contactID, model.commId, model.communicationTypeID, model.subject, model.communicationDate, model.assignedEmployeeID, model.productID, model.officeId, model.notes, model.followUpdate, model.statusID, model.phone, model.emailTo);
            if (!string.IsNullOrEmpty(model.note))
            {
                this.Bridge.ScratchUtility.InsertCommunicationFeedBack(model.logInOwner, model.note, id);
            }
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;

                #region insert current  user as assigined employee by default
                DataRow empDr = this.Bridge.EmployeeUtility.GetEmpIdByUser(model.userID);
                int empIdByDefault = Convert.ToInt32(empDr["EmployeeId"]);
                this.Bridge.CommEmployeeUtility.Insert(id, empIdByDefault, DateTime.Now);

                #endregion
                if (model.emailTo == "")
                    model.emailTo = "resnav@resnav.com";
                //All Contacts iteration
                List<EmailVM> emailVmList = new List<EmailVM>();
                if (!string.IsNullOrEmpty(model.AllContactIds))
                {
                    foreach (var item in model.AllContactIds.Split(','))
                    {
                        int commId = id;
                        int conAllId = Convert.ToInt32(item);
                        this.Bridge.CommunicationContacts.Insert(commId, conAllId, DateTime.Now);

                        this.Bridge.CompanyContactsUtility.Insert(commId, conAllId);

                        #region send email to a contact and cc email to the current user
                        if (model.communicationTypeID == 2)
                        {
                            //get email id for the contacts to send the email.
                            List<EmailVM> emailTo = this.Bridge.CommunicationContacts.GetContactEmails(conAllId);
                            emailVmList.AddRange(emailTo);
                        }
                        #endregion
                    }
                }

                returnMessage.Message = "Record has been inserted";


                //Comm Employee Contacts iteration
                //List<EmailVM> emailAEList = new List<EmailVM>();
                if (!string.IsNullOrEmpty(model.CommEmployeeIds))
                {
                    foreach (var item in model.CommEmployeeIds.Split(','))
                    {
                        int commId = id;
                        int comEmpId = Convert.ToInt32(item);

                        this.Bridge.CommunicationContacts.InsertEmployee(commId, comEmpId, DateTime.Now);

                        #region send email to a contact and cc email to the current user
                        if (model.communicationTypeID == 2)
                        {
                            //get email id for the contacts to send the email.

                            List<EmailVM> emailTo = this.Bridge.CommunicationContacts.GetContactEmails(comEmpId);

                            emailVmList.AddRange(emailTo);
                            #endregion
                        }
                    }
                }



                //Company Contacts iteration
                List<EmailVM> companycontList = new List<EmailVM>();
                if (!string.IsNullOrEmpty(model.CompanyContactIds))
                {
                    foreach (var item in model.CompanyContactIds.Split(','))
                    {
                        int commId = id;
                        int comContId = Convert.ToInt32(item);
                        //if (this.Bridge.CompanyContactsUtility.Insert(model.companyId, comContId) <= 0)
                        if (this.Bridge.CompanyContactsUtility.InsertToComContact(commId, comContId, DateTime.Now) <= 0)
                            //this.Bridge.CompanyContactsUtility.Insert(commId, comContId);

                            #region send email to a contact and cc email to the current user
                            if (model.communicationTypeID == 2)
                            {
                                //get email id for the contacts to send the email.
                                List<EmailVM> emailTo = this.Bridge.CommunicationContacts.GetContactEmails(comContId);

                                emailVmList.AddRange(emailTo);
                            }
                        #endregion
                    }
                }

                returnMessage.Message = "Record has been inserted";
                try
                {

                    List<string> AllContactList = emailVmList.Where(e => e.email != null && e.email != "").Select(e => e.email).Distinct().ToList();
                    string[] to = AllContactList.ToArray();
                    string[] cc = { model.emailTo };
                    WebMail.SendMail(to, cc, model.subject, model.notes, null);
                    returnMessage.Message += "and Mails were sended to corresponding contacts";
                    returnMessage.Status = true;
                }
                catch
                {
                    returnMessage.Message += " But Some of the contact doesn't have an email address on file!!";
                }

            }
            return returnMessage;
        }


        public ReturnMessage<CompanyCommunicationViewModel> UpdateToCompanyComm(CompanyCommunicationViewModel model)
        {
            var returnMessage = new ReturnMessage<CompanyCommunicationViewModel>();

            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";
            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";
            if (String.IsNullOrEmpty(model.subject))
                model.subject = "";

            if (String.IsNullOrEmpty(model.logInOwner))
                model.logInOwner = "";
            if (String.IsNullOrEmpty(model.emailTo))
                model.emailTo = "";

            if (model.emailTo == "")
                model.emailTo = "resnav@resnav.com";
            model.createdDate = DateTime.Now;
            int id = 0;
            try
            {
                id = this.UpdateById(model.id, model.userID, model.logInOwner, model.companyId, model.contactID, model.communicationTypeID, model.subject, model.assignedEmployeeID, model.productID, model.officeId, model.notes, model.followUpdate, model.communicationDate, model.statusID, model.phone, model.emailTo);
                if (!string.IsNullOrEmpty(model.note))
                {
                    this.Bridge.ScratchUtility.InsertCommunicationFeedBack(model.logInOwner, model.note, id);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }


            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;


                this.Bridge.CommunicationContacts.Delete(model.id);
                this.Bridge.CommEmployeeUtility.Delete(model.id);

                this.DeleteCompanyContacts(model.id);

                #region insert current  user as assigined employee by default
                DataRow empDr = this.Bridge.EmployeeUtility.GetEmpIdByUser(model.userID);
                int empIdByDefault = Convert.ToInt32(empDr["EmployeeId"]);
                this.Bridge.CommEmployeeUtility.Insert(id, empIdByDefault, DateTime.Now);

                #endregion
                if (model.emailTo == "")
                    model.emailTo = "resnav@resnav.com";
                //All Contacts iteration
                if (!string.IsNullOrEmpty(model.AllContactIds))
                {
                    foreach (var item in model.AllContactIds.Split(','))
                    {
                        int commId = id;
                        int conAllId = Convert.ToInt32(item);
                        this.Bridge.CommunicationContacts.Insert(commId, conAllId, DateTime.Now);
                        //this.Bridge.CompanyContactsUtility.Insert(model.companyId, conAllId); Jayesh
                        this.Bridge.CompanyContactsUtility.Insert(commId, conAllId);

                        #region send email to a contact and cc email to the current user
                        if (model.commId == 2)
                        {
                            //get email id for the contacts to send the email.

                            string emailTo = Convert.ToString(this.Bridge.CommunicationContacts.GetContEmail(conAllId));
                            if (emailTo != "" || emailTo != null)
                            {
                                string[] to = { emailTo, model.emailTo };
                                string[] cc = { };
                                WebMail.SendMail(to, cc, model.subject, model.notes, null);
                                //Email.sendEmail(emailTo, subject, notes);
                            }
                            else
                            {
                                returnMessage.Message = "Some of the contact doesn't have an email address on file!!";

                            }
                        }
                        #endregion
                    }
                }
                returnMessage.Message = "Record has been inserted";
                returnMessage.Status = true;


                //Comm Employee Contacts iteration
                if (!string.IsNullOrEmpty(model.CommEmployeeIds))
                {
                    foreach (var item in model.CommEmployeeIds.Split(','))
                    {
                        int commId = id;
                        int comEmpId = Convert.ToInt32(item);

                        this.Bridge.CommunicationContacts.InsertEmployee(commId, comEmpId, DateTime.Now);

                        #region send email to a contact and cc email to the current user
                        if (model.commId == 2)
                        {
                            //get email id for the contacts to send the email.

                            string emailTo = Convert.ToString(this.Bridge.CommunicationContacts.GetContEmail(comEmpId));
                            if (emailTo != "" || emailTo != null)
                            {
                                string[] to = { emailTo, model.email };
                                string[] cc = { };
                                WebMail.SendMail(to, cc, model.subject, model.notes, null);
                                //Email.sendEmail(emailTo, subject, notes);
                            }
                            else
                            {
                                returnMessage.Message = "Some of the contact doesn't have an email address on file!!";

                            }
                        }
                        #endregion
                    }
                }
                returnMessage.Message = "Record has been inserted";
                returnMessage.Status = true;



                //Company Contacts iteration
                if (!string.IsNullOrEmpty(model.CompanyContactIds))
                {
                    foreach (var item in model.CompanyContactIds.Split(','))
                    {
                        int commId = id;
                        int comContId = Convert.ToInt32(item);
                        //if (this.Bridge.CompanyContactsUtility.Insert(model.companyId, comContId) <= 0) -- Jayesh 
                        if (this.Bridge.CompanyContactsUtility.InsertToComContact(commId, comContId, DateTime.Now) <= 0)

                            #region send email to a contact and cc email to the current user
                            if (model.commId == 2)
                            {
                                //get email id for the contacts to send the email.

                                string emailTo = Convert.ToString(this.Bridge.CommunicationContacts.GetContEmail(comContId));
                                if (emailTo != "" || emailTo != null)
                                {
                                    string[] to = { emailTo, model.email };
                                    string[] cc = { };
                                    WebMail.SendMail(to, cc, model.subject, model.notes, null);
                                    //Email.sendEmail(emailTo, subject, notes);
                                }
                                else
                                {
                                    returnMessage.Message = "Some of the contact doesn't have an email address on file!!";

                                }
                            }
                        #endregion
                    }
                }
                returnMessage.Message = "Record has been inserted";
                returnMessage.Status = true;

            }
            return returnMessage;
        }

        public int Insert(int userId, string logInOwner, DateTime createdDate, string inOrOut,string feedback, int companyId, int contactid, int commId, int communicationTypeID,
                      string subject, Nullable<DateTime> commDate, int assignEmpId, int prodId, int officeId,
                      string notes, Nullable<DateTime> followUpDate, int statusId, string phone, string emailTo)
        {
            int nRtn = -1;

            string sql = "Insert INTO ccCommunications " +
                                "(userId,logInOwner,createdDate, inOrOut,feedback, clientId,contactid,commId,communicationTypeID,subject," +
                                "communicationDate,assignedEmployeeID,productID,officeId,notes,followUpDate,statusId, phone, emailTo) " +
                                "Values(@userId, @logInOwner,@createdDate,@inOrOut, @feedback,@clientId,@contactid,@commId,@communicationTypeID,@subject," +
                                " @commDate, @assignEmpId,@prodId,@officeId,@notes,@followUpDate, @statusId, @phone, @emailTo) SELECT @pResult =@@IDENTITY";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.Int, 10, userId);
                this.Factory.AddParameter(cmd, "@logInOwner", SqlDbType.NVarChar, 50, logInOwner);
                this.Factory.AddParameter(cmd, "@createdDate", SqlDbType.DateTime, 100, createdDate);
                this.Factory.AddParameter(cmd, "@inOrOut", SqlDbType.NVarChar, 100, inOrOut);
                this.Factory.AddParameter(cmd, "@feedback", SqlDbType.NVarChar, 1000, feedback);
                this.Factory.AddParameter(cmd, "@clientId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@contactid", SqlDbType.Int, 10, contactid);
                this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 10, commId);
                this.Factory.AddParameter(cmd, "@communicationTypeID", SqlDbType.Int, 10, communicationTypeID);
                this.Factory.AddParameter(cmd, "@subject", SqlDbType.VarChar, 250, subject);

                this.Factory.AddParameter(cmd, "@assignEmpId", SqlDbType.Int, 10, assignEmpId);
                this.Factory.AddParameter(cmd, "@prodId", SqlDbType.Int, 10, prodId);
                this.Factory.AddParameter(cmd, "@officeId", SqlDbType.Int, 10, officeId);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.Text, 1000, notes);

                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);
                this.Factory.AddParameter(cmd, "@phone", SqlDbType.Text, 10, phone);
                this.Factory.AddParameter(cmd, "@emailTo", SqlDbType.Text, 1000, emailTo);


                if ((followUpDate == null) || (followUpDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@followUpDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@followUpDate", SqlDbType.DateTime, 10, followUpDate);


                if ((commDate == null) || (commDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@commDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@commDate", SqlDbType.DateTime, 10, commDate);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }

            return nRtn;//retruning the data stored in the table


        }

        public int Update(int commId, int userId, string logInOwner,DateTime createdDate,string inOrOut, Nullable<DateTime> commDate, string feedback, int companyId, int contactid, int communicationTypeID,
                        string subject, int assignEmpId, int prodId,
                        string notes, Nullable<DateTime> followUpDate, int statusId)
        {
            int nRtn = -1;

            string sql = "UPDATE ccCommunications SET " +
                                      "userId=@userId, logInOwner = @logInOwner, createdDate=@createdDate, inOrOut=@inOrOut , communicationDate=@communicationDate,  feedback = @feedback, clientID=@companyId,contactid=@contactid, communicationTypeID=@communicationTypeID,subject=@subject," +
                                      "assignedEmployeeID=@assignEmpId,productId=@prodId,notes=@notes," +
                                      "followUpDate=@followUpDate, statusId=@statusId WHERE commId = @commId";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.Int, 10, userId);
                this.Factory.AddParameter(cmd, "@logInOwner", SqlDbType.NVarChar, 50, logInOwner);
                this.Factory.AddParameter(cmd, "@feedback", SqlDbType.NVarChar, 10000, feedback);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@contactid", SqlDbType.Int, 10, contactid);
                this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 10, commId);
                this.Factory.AddParameter(cmd, "@communicationTypeID", SqlDbType.Int, 10, communicationTypeID);
                this.Factory.AddParameter(cmd, "@subject", SqlDbType.VarChar, 250, subject);

                this.Factory.AddParameter(cmd, "@assignEmpId", SqlDbType.Int, 10, assignEmpId);
                this.Factory.AddParameter(cmd, "@prodId", SqlDbType.Int, 10, prodId);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.Text, 1000, notes);
              
                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);


                if ((followUpDate == null) || (followUpDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@followUpDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@followUpDate", SqlDbType.DateTime, 10, followUpDate);


                if ((commDate == null) || (commDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@commDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@commDate", SqlDbType.DateTime, 10, commDate);


                this.Factory.ExecuteNonQuery(cmd);
            }
            nRtn = commId;
            return nRtn; //retruning the data stored in the table            
        }

        public int UpdateById(int id, int userId, string logInOwner, int companyId, int contactid, int communicationTypeID,
                       string subject, int assignEmpId, int prodId, int officeId,
                       string notes, Nullable<DateTime> followUpDate, Nullable<DateTime> commDate, int statusId, string phone, string email)
        {
            int nRtn = -1;

            string sql = "UPDATE ccCommunications SET " +
                                     "userId=@userId,logInOwner= @logInOwner, clientID=@companyId,contactid=@contactid,communicationTypeID=@communicationTypeID,subject=@subject," +
                                     "assignedEmployeeID=@assignEmpId,productId=@prodId, officeId=@officeId, notes=@notes," +
                                     "followUpDate=@followUpDate, communicationDate = @commDate, statusId=@statusId, emailTo=@emailTo, phone=@phone WHERE id = @id";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.Int, 10, userId);
                this.Factory.AddParameter(cmd, "@logInOwner", SqlDbType.NVarChar, 50, logInOwner);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);

                this.Factory.AddParameter(cmd, "@contactid", SqlDbType.Int, 10, contactid);
                this.Factory.AddParameter(cmd, "@communicationTypeID", SqlDbType.Int, 10, communicationTypeID);
                this.Factory.AddParameter(cmd, "@subject", SqlDbType.VarChar, 250, subject);
                this.Factory.AddParameter(cmd, "@assignEmpId", SqlDbType.Int, 10, assignEmpId);
                this.Factory.AddParameter(cmd, "@prodId", SqlDbType.Int, 10, prodId);
                this.Factory.AddParameter(cmd, "@officeId", SqlDbType.Int, 10, officeId);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.Text, 1000, notes);

                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);
                this.Factory.AddParameter(cmd, "@phone", SqlDbType.Text, 10, phone);
                this.Factory.AddParameter(cmd, "@emailTo", SqlDbType.Text, 10, email);


                if ((followUpDate == null) || (followUpDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@followUpDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@followUpDate", SqlDbType.DateTime, 10, followUpDate);


                if ((commDate == null) || (commDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@commDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@commDate", SqlDbType.DateTime, 10, commDate);

                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }

        public void Delete(int id) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM ccCommunications " +
                   "WHERE ID = @ID " +
                   "DELETE FROM ccCommcontact " +
                   "WHERE commID = @ID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);

                this.Factory.ExecuteNonQuery(cmd);
            }
        }


        public void DeleteCompanyContacts(int id) //delete the row of data using the id passed to the function
        {

            string sql = "Delete from ddlCompanyContacts where companyid = @ID";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);

                this.Factory.ExecuteNonQuery(cmd);
            }
        }

        public DataTable GetAll()
        {
            string sql = "SELECT * FROM ccCommunications";

            return this.Factory.ExecuteTable(sql);

        }
        public DataRow GetTheId(int commId)
        {
            string sql = "SELECT * FROM ccCommunications WHERE commId = " + commId;

            return this.Factory.ExecuteRecord(sql);

        }

        public List<CompanyCommunicationViewModel> GetAllGrid(int companyId, int userId)
        {
            string sql = " SELECT DISTINCT c.*,   dc.name as companyName, c.logInOwner ," +
                              " p.product, s.description  as statusName, cm.description  as commVia, co.name as companyoffice,  cm.Code as communicationTypeCode,  " +

                                 " STUFF((SELECT ',' +  CAST(contactId AS VARCHAR(MAX)) [text()] " +
                                " FROM ccCommcontact WHERE commId = c.id " +
                               " FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS AllContactIds, " +

                           " STUFF((SELECT ', ' +  CAST(cct.firstName AS VARCHAR(MAX))+ ' ' + CAST(cct.lastName AS VARCHAR(MAX)) [text()] " +
                            " FROM ccCommcontact ccc INNER JOIN ccContacts cct ON cct.id = ccc.contactId WHERE ccc.commId = c.id " +
                            " FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') AS AllContacts , " +

                          " STUFF((SELECT ',' +  CAST(contactId AS VARCHAR(MAX)) [text()] " +
                                " FROM ccCommcontact WHERE commId = c.id " +
                               " FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS CompanyContactIds, " +

                              " STUFF((SELECT ', ' +  CAST(cct.firstName AS VARCHAR(MAX))+ ' ' + CAST(cct.lastName AS VARCHAR(MAX)) [text()] " +
                            " FROM ccCommcontact ccc INNER JOIN ccContacts cct ON cct.id = ccc.contactId WHERE ccc.commId = c.id " +
                            " FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') AS CompanyContacts , " +


//       " STUFF((SELECT ',' + CAST(contactId AS VARCHAR(MAX))[text()]  " +
//         " FROM ddlCompanyContacts WHERE contactId = c.id " +
//" FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') AS CompanyContactIds, " +

//  " STUFF((SELECT ', ' + CAST(cct.firstName AS VARCHAR(MAX)) + ' ' + CAST(cct.lastName AS VARCHAR(MAX))[text()] " +
//"  FROM ddlCompanyContacts compcc INNER JOIN ccContacts cct ON cct.id = compcc.contactId WHERE compcc.contactId = c.id " +
//" FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,' ') AS CompanyContacts , " +





"          STUFF((SELECT ',' + CAST(cae.id AS VARCHAR(MAX))[text()] " +
"            from ccCommEmployee aae INNER JOIN ddlEmployees bae on aae.employeeId = bae.contactId INNER JOIN ccContacts cae on cae.id = aae.employeeId where aae.commId = c.id " +
"            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') AS CommEmployeeIds, " +

"            STUFF((SELECT ', ' + CAST(cae.firstName AS VARCHAR(MAX)) + ' ' + CAST(cae.lastName AS VARCHAR(MAX))[text()] " +
"          from ccCommEmployee aae INNER JOIN ddlEmployees bae on aae.employeeId = bae.contactId INNER JOIN ccContacts cae on cae.id = aae.employeeId where aae.commId = c.id " +
"           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,' ') AS CommEmployee ," +

 "(SELECT replace(REPLACE(replace(isnull(STUFF((SELECT top(5) ' ' + SN.CreatedUser + ' : ' + SN.note + ' <br/> ' " +

  "  FROM tbl_Communication_feedback as SN   where SN.CommId = c.id order by sn.id desc FOR XML PATH('')), 1, 1, ''), ''), '&lt;', '<'), '&', '>'),'gt;','')) " +
   " as FeedbackNote " +


                                     " FROM ccCommunications c " +
                                     " LEFT JOIN ddlCompany dc ON dc.id = c.clientId " +
                                     " LEFT JOIN ddlStatus s ON s.id = c.statusId " +
                                     " INNER JOIN ddlProducts p ON p.id = c.productId " +
                                     " LEFT JOIN ddlCommunicationMedium cm ON cm.id = c.communicationTypeID " +
                                     " LEFT JOIN ccCompanyOffice co ON co.id = c.officeId " +
                                     " WHERE c.clientId = " + companyId + " ORDER BY c.createdDate DESC ";

            return this.Factory.ExecuteObjects<CompanyCommunicationViewModel>(sql);

        }

        public DataTable GetAllGridContact(int contactId, int userId)
        {
            string sql = " SELECT c.*, cc.lastname+', '+cc.firstname as heldContactName, dc.name as companyName, " +
                                    " (SELECT TOP 1 lastName+', '+firstName FROM ddlEmployees e INNER JOIN ccContacts " +
                                    " on ccContacts.id = e.contactId WHERE ccContacts.id = c.assignedEmployeeID) as assignedEmp, " +
                                    " p.product, s.description  as status, cm.description communication, co.name as office " +
                                    " FROM ccCommunications c " +
                                    " INNER JOIN ccCommcontact ccc ON ccc.commId = c.id " +
                                    " INNER JOIN ccContacts cc ON cc.id = ccc.contactId  " +
                                    " LEFT JOIN ddlCompany dc ON dc.id = c.clientId " +
                                    " LEFT JOIN ddlStatus s ON s.id = c.statusId " +
                                    " INNER JOIN ddlProducts p ON p.id = c.productId " +
                                    " LEFT JOIN ddlCommunicationMedium cm ON cm.id = c.communicationTypeID " +
                                    " LEFT JOIN ccCompanyOffice co ON co.id = c.officeId " +
                                    " WHERE ccc.contactId = " + contactId + " ORDER BY c.communicationDate DESC";

            return this.Factory.ExecuteTable(sql);

        }
        public List<CompanyCommunicationViewModel> GetContactMSG(int contactId, int userId)
        {
            string sql = " SELECT DISTINCT c.*, cc.lastname+', '+cc.firstname as heldContactName, dc.name as company, " +
           " STUFF((SELECT ',' + CAST(aae.contactId AS VARCHAR(MAX))[text()]   " +
           " from ccCommcontact aae  where aae.commId = c.id " +
           " FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') AS CommEmployeeIds, " +

           " STUFF((SELECT ', ' + CAST(x.firstName AS VARCHAR(MAX)) + ' ' + CAST(x.lastName AS VARCHAR(MAX))[text()] " +
         " from ccContacts x INNER JOIN ddlEmployees y ON y.contactId = x.id INNER JOIN ccCommcontact z ON z.contactid = x.id where z.commId = c.id " +
         " FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,' ') AS CommEmployee, " +





                  " (SELECT TOP 1 lastName+', '+firstName FROM ddlEmployees e " +
                  " INNER JOIN ccContacts  on ccContacts.id = e.contactId " +
                  " WHERE ccContacts.id = c.assignedEmployeeID) as assignedEmp, " +
                  " p.product, s.description  as statusName,  cm.description as commVia, cm.Code as communicationTypeCode,  " +
                  " co.name as companyoffice  FROM ccCommunications c " +
                  " LEFT JOIN ccContacts cc ON cc.id = c.contactId " +
                  " INNER JOIN ccCommcontact com on com.commId = c.id " +
                  " LEFT JOIN ccContacts cont on cont.id = com.contactId " +
                  " LEFT JOIN ScratchCallLogs sc on sc.id = c.commId " +
                  " LEFT JOIN ddlCompany dc ON dc.id = c.clientId " +
                  " LEFT JOIN ddlStatus s ON s.id = c.statusId " +
                  " INNER JOIN ddlProducts p ON p.id = c.productId " +
                  " INNER JOIN ddlCommunicationMedium cm ON cm.id = c.communicationTypeID " +
                  " LEFT JOIN ccCompanyOffice co ON co.id = c.officeId " +
                  " WHERE c.contactId= " + contactId + " OR com.contactId= " + contactId + " ORDER BY c.communicationDate DESC ";

            return this.Factory.ExecuteObjects<CompanyCommunicationViewModel>(sql);

        }

        public List<CommunicationMediumViewModel> GetAllCommunicationMedium() //Gets all the data form the contacts table
        {
            string sql = "Select * from ddlCommunicationMedium";

            return this.Factory.ExecuteObjects<CommunicationMediumViewModel>(sql);
        }

        public List<ContactGridViewModel> GetAllContactsForMsg()
        {

            string sql = "SELECT cs.id as id, cs.firstName + ' ' + cs.lastName as contactname, cs.*, " +
            " cs.businessPhone, cs.companyId as company, cs.email, cp.name as cpName, ct.description as contactTypeName, " +
            " CONVERT(date, cs.enteredDate) as callDate from ccContacts cs " +
            " LEFT JOIN ddlCompany cp on cp.id = cs.companyId " +
            " LEFT JOIN ddlContactTypes ct on ct.id= cs.contactType order by contactname";

            return this.Factory.ExecuteObjects<ContactGridViewModel>(sql);
        }


        public int GetCommunicationIdByScratchCallLogId(int id)
        {

            int result = 0;
            string sql = string.Format("select top(1) id from ccCommunications where commId = '" + id + "'");
            try
            {
                result = Convert.ToInt32(this.Factory.ExecuteScalar(sql));
            }
            catch { }
            return result;


        }


        public int InsertToMessageUpload(int MessageId, string URL, string FileName) //insert data to a contact table
        {
            int nRtn = -1;
            string sql = "INSERT INTO tbl_MessageUpload (MessageId,URL, Name) Values(@MessageId, @URL, @Name) SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@MessageId", SqlDbType.Int, 10, MessageId);
                this.Factory.AddParameter(cmd, "@URL", SqlDbType.NVarChar, 1000, URL);
                this.Factory.AddParameter(cmd, "@Name", SqlDbType.NVarChar, 50, FileName);
                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }

        public List<MessageUploadVM> GetMessageUploadfiles(int MessageId)
        {
            string sql = "select *, Id as id from tbl_MessageUpload where IsActive is null and MessageId = " + MessageId;

            return this.Factory.ExecuteObjects<MessageUploadVM>(sql);
        }

        public void DeleteUploadFiles(int messageId, int Id) //delete the row of data using the id passed to the function
        {
            string sql = " update tbl_MessageUpload set IsActive = 0 where MessageId = " + messageId + "  and id = " + Id;
            this.Factory.ExecuteNonQuery(sql);

        }

    }
}